package com.cola.dao;

import com.cola.entity.export.ExportMep;
import com.cola.utils.DBControl;
import com.cola.utils.DateUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

/**
 * MepDao经销商
 * @author Max
 * @since 2017/9/6
 */
public class MepDao extends BaseDao{
    private final Logger logger = LoggerFactory.getLogger(getClass());
    private static volatile MepDao instance = null;

    public static MepDao getInstance() {
        if (instance == null) {
            synchronized (MepDao.class) {
                if (instance == null) {
                    instance = new MepDao();
                }
            }
        }
        return instance;
    }


    /**
     * 获取经销商列表
     * @param beginNum 搜索起始位置
     * @param pageSize 页面大小
     * @param filter 搜索条件
     * @return List<Map<String,Object>> 经销商信息列表
     * @author Max
     * @since 2017/9/6
     */
    public List<Map<String,Object>> getMepList(Integer beginNum,Integer pageSize,String filter){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select a.*,b.area_name from cola_mep a inner join system_area b on a.city = b.area_no  where 1=1  "+filter+" order by mep_id desc limit ?,? ";
            return getQueryRunner().query(conn,sql,new MapListHandler(),beginNum,pageSize);
        }catch (Exception e){
            logger.error("获取经销商列表异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 获取经销商数量
     * @param filter 搜索条件
     * @return Integer数量
     * @author Max
     * @since 2017/9/6
     */
    public Integer getMepCount(String filter){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select count(mep_id) from cola_mep a where 1=1  "+filter;
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                return rs.getInt(1);
            }
        }catch (Exception e){
            logger.error("获取经销商总数异常！",e);
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return 0;
    }

    /**
     * 根据id获取Mep详细信息
     * @param mepId 经销商内部id
     * @return  Map<String,Object> 经销商信息
     * @author Max
     * @since 2017/9/6
     */
    public Map<String,Object> getMepInfoById(Integer mepId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_mep where mep_id = ? ";
            return getQueryRunner().query(conn,sql,new MapHandler(),mepId);
        }catch (Exception e){
            logger.error("根据mepId获取Mep信息异常",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 保存Mep信息
     * @param id 经销商id
     * @param name 经销商名称
     * @param mobile 经销商手机号
     * @param contact_name 经销商联系人
     * @param address 经销商地址
     * @param city 经销商城城市
     * @return true 成功;false 失败
     * @author Max
     * @since 2017/9/6
     */
    public boolean saveMepInfo(Integer id,String name,String mobile,String contact_name,String address,String city ,Double mep_lat,Double mep_lng){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            if(mep_lat>0 && mep_lng>0){
                String sql = "update cola_mep set mep_mobile = ?,city =? ,mep_contact_name=?,address=?,mep_name=?,mep_lat =?,mep_lng =? where mep_id =?";
                return getQueryRunner().update(conn,sql,mobile,city,contact_name,address,name,mep_lat,mep_lng,id)>0;
            }else{
                String sql = "update cola_mep set mep_mobile = ?,city =? ,mep_contact_name=?,address=?,mep_name=? where mep_id =?";
                return getQueryRunner().update(conn,sql,mobile,city,contact_name,address,name,id)>0;
            }
        }catch (Exception e){
            logger.error("更新Mep信息异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 检查经销商编号是否存在
     * @param mobile
     * @return
     */
    public Integer checkMepNo(String mepNo){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBControl.getConnection();
            String sql= "select count(mep_id) from cola_mep where mep_no = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,mepNo);
            rs = ps.executeQuery();
            if(rs.next()){
                return rs.getInt(1);
            }
        }catch (Exception e){
            logger.error("检查经销商编号是否存在异常！",e);
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return -1;
    }

    /**
     * 添加Mep信息
     * @param mepInfo
     * @return
     */
    public boolean addMepInfo(Map<String,Object> mepInfo){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "insert into cola_mep(mep_name,mep_no,city,address,mep_mobile,mep_contact_name,mep_lat,mep_lng,is_bind,status,create_time) " +
                    " values(?,?,?,?,?,?,?,?,0,1,?)";
            return getQueryRunner().update(conn,sql,mepInfo.get("mep_name"),mepInfo.get("mep_no"),mepInfo.get("city"),mepInfo.get("address"),
                    mepInfo.get("mep_mobile"),mepInfo.get("mep_contact_name"),mepInfo.get("mep_lat"),mepInfo.get("mep_lng"),
                    DateUtils.getCurrentDateTime())>0;
        }catch (Exception e){
            logger.error("导入Mep信息新增Mep异常!",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 更新MEP信息
     * @param mepInfo
     * @return
     */
    public boolean updateMepInfo(Map<String,Object> mepInfo){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "update cola_mep "+getUpdateSql(mepInfo)+" where mep_no=?";
            return getQueryRunner().update(conn,sql,mepInfo.get("mep_no"))>0;
        }catch (Exception e){
            logger.error("导入经销商数据更新数据异常!",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }

        return false;
    }

    private String getUpdateSql(Map<String,Object> mepInfo){
        StringBuilder sql = new StringBuilder();
        sql.append(" set update_time=now() ");

        if(mepInfo.containsKey("mep_mobile")){
            sql.append(" ,mep_mobile= '").append(mepInfo.get("mep_mobile")).append("' ");
        }

        if(mepInfo.containsKey("city")){
            sql.append(" ,city= '").append(mepInfo.get("city")).append("' ");
        }

        if(mepInfo.containsKey("address")){
            sql.append(" ,address= '").append(mepInfo.get("address")).append("' ");
        }

        if(mepInfo.containsKey("mep_name")){
            sql.append(" ,mep_name= '").append(mepInfo.get("mep_name")).append("' ");
        }

        if(mepInfo.containsKey("mep_contact_name")){
            sql.append(" ,mep_contact_name= '").append(mepInfo.get("mep_contact_name")).append("' ");
        }

        if(mepInfo.containsKey("mep_lng")){
            sql.append(" ,mep_lng= ").append(mepInfo.get("mep_lng"));
        }

        if(mepInfo.containsKey("mep_lat")){
            sql.append(" ,mep_lat= ").append(mepInfo.get("mep_lat"));
        }

        return sql.toString();
    }

    /**
     * 删除经销商
     * @param mepId 经销商内部id
     * @param status 状态
     * @return true 成功;false 失败
     * @author Max
     * @since 2017/9/6
     */
    public boolean updateStatusMep(Integer mepId,Integer status){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "update cola_mep set status =? where mep_id =?";
            return getQueryRunner().update(conn,sql,status,mepId)>0;
        }catch (Exception e){
            logger.error("禁封经销商异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 获取经销商导入记录
     * @param offset 起始位置
     * @param limit 页面大小
     * @return  List<Map<String,Object>> 经销商信息列表
     * @author Max
     * @since 2017/9/6
     */
    public List<Map<String,Object>> getImportRecordList(Integer offset,Integer limit){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_mep_import_log where status =1 order by log_id desc limit ?,?";
            return getQueryRunner().query(conn, sql, new MapListHandler(),offset,limit);
        }catch (Exception e){
            logger.error("获取经销商导入记录异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 获取导入经销商记录数
     * @return Integer 记录数
     * @author Max
     * @since 2017/9/6
     */
    public Integer getMepImportRecordCount(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select count(*) from cola_mep_import_log where status =1";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                return rs.getInt(1);
            }
        }catch (Exception e){
            logger.error("获取导入MEP记录数异常！",e);
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return 0;
    }


    /**
     * 获取导出MEP信息列表
     * @return  List<ExportMep> 经销商列表
     * @author Max
     * @since 2017/9/6
     */
    public List<ExportMep> getExportMepList(){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.mep_no,mep_name,a.city as city_no ,b.area_name as city,address,mep_mobile,mep_contact_name,a.`status`,a.is_bind FROM `cola_mep` a\n" +
                    " inner JOIN system_area b on a.city = b.area_no;";
            return getQueryRunner().query(conn,sql,new BeanListHandler<ExportMep>(ExportMep.class));
        }catch (Exception e){
            logger.error("获取导出MEP信息列表异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }



    public static void main(String[] args) {
        System.out.println(getInstance().checkMepNo("18686803278"));
    }
}
